package be.groept.ace.enquete.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.SortedSet;
import java.util.TreeSet;

import be.groept.ace.enquete.model.beans.User;
import be.groept.ace.enquete.model.enums.Language;
import be.groept.ace.enquete.model.exceptions.DAOException;
import be.groept.ace.enquete.utils.SQLUtils;

public class UserDAOJDBCImpl implements UserDAO {

	private Connection connection;
	
	public UserDAOJDBCImpl(Connection connection) {
		this.connection = connection;
	}
	@Override
	public void insertUser(User user) throws DAOException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			Timestamp ts = SQLUtils.getCurrentDate();
			String sql = "INSERT INTO User (name, firstname, address, language, email, password, administrator, creation) VALUES  (?, ?, ?, ?, ?, ?, ?, ? )";
			preparedStatement = connection.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getFirstname());
			preparedStatement.setString(3, user.getAddress());
			preparedStatement.setString(4, user.getLanguage().equals(Language.EN) ? "EN" : "NL");
			preparedStatement.setString(5, user.getEmail());
			preparedStatement.setString(6, user.getPassword());
			preparedStatement.setBoolean(7, user.isAdministrator());
			preparedStatement.setTimestamp(8, ts);
			preparedStatement.executeUpdate();
			resultSet = preparedStatement.getGeneratedKeys();
			resultSet.next();
			user.setId(resultSet.getInt(1));
			user.setCreation(ts);
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
	}

	@Override
	public void deleteUser(User user) throws DAOException {
		if (user.getId() <= 0) {
			throw new DAOException(String.format("%s was not yet persisted to the database", user));
		}
		PreparedStatement preparedStatement = null;
		try {
			String sql = "DELETE FROM User WHERE id = ?";
			preparedStatement = connection.prepareStatement( sql );
			preparedStatement.setInt(1, user.getId());
			preparedStatement.executeUpdate();
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
	}

	@Override
	public User findUser(String email, String password) throws DAOException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			String sql = "SELECT id, name, firstname, address, language, email, password, administrator, creation FROM User WHERE email = ? AND password = ?";
			preparedStatement = connection.prepareStatement( sql );
			preparedStatement.setString(1, email);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()) {
				user = map(resultSet);
			}
			
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return user;
	}

	@Override
	public void updateUser(User user) throws DAOException {
		if (user.getId() <= 0) {
			throw new DAOException(String.format("%s was not yet persisted to the database", user));
		}
		
		PreparedStatement preparedStatement = null;
		try {
			String sql = "UPDATE User SET name = ?, firstname = ?, address = ?, language = ?, email = ?, password = ?, administrator = ? WHERE id = ?";
			preparedStatement = connection.prepareStatement( sql );
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getFirstname());
			preparedStatement.setString(3, user.getAddress());
			preparedStatement.setString(4, user.getLanguage().equals(Language.EN) ? "EN" : "NL");
			preparedStatement.setString(5, user.getEmail());
			preparedStatement.setString(6, user.getPassword());
			preparedStatement.setBoolean(7, user.isAdministrator());
			preparedStatement.setInt(8, user.getId());
			preparedStatement.executeUpdate();
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
	}

	@Override
	public SortedSet<User> findAllUsers() throws DAOException {
		SortedSet<User> userSet = new TreeSet<User>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT id, name, firstname, address, language, email, password, administrator, creation FROM User";
			preparedStatement = connection.prepareStatement( sql );
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				User user = map(resultSet);
				userSet.add(user);
			}
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return userSet;
	}
	
	@Override
	public User findUserByPK(int id) throws DAOException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			String sql = "SELECT id, name, firstname, address, language, email, password, administrator, creation FROM User WHERE id = ?";
			preparedStatement = connection.prepareStatement( sql );
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()) {
				user = map(resultSet);
			}
			
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return user;
	}
	
	public User map(ResultSet resultSet) throws SQLException {
		User user = new User();
		user.setId(resultSet.getInt("user.id"));
		user.setName(resultSet.getString("user.name"));
		user.setFirstname(resultSet.getString("user.firstname"));
		user.setAddress(resultSet.getString("user.address"));
		user.setLanguage(resultSet.getString("user.language").equals("EN") ? Language.EN : Language.NL);
		user.setEmail(resultSet.getString("user.email"));
		user.setPassword(resultSet.getString("user.password"));
		user.setAdministrator(resultSet.getBoolean("user.administrator"));
		user.setCreation(resultSet.getTimestamp("user.creation"));
		return user;
	}
	@Override
	public User findUserByEmail(String email) throws DAOException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			String sql = "SELECT id, name, firstname, address, language, email, password, administrator, creation FROM User WHERE email = ?";
			preparedStatement = connection.prepareStatement( sql );
			preparedStatement.setString(1, email);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()) {
				user = map(resultSet);
			}
			
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return user;
	}
	
	
}
